/*
 * BookSeatCommand.java
 *
 * Created on October 4, 2007, 7:10 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.io.BufferedReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;

/**
 *
 * @author Asger
 */
public class BookSeatCommand extends PromptingCommand {
    
    private FindSeatsCommand findSeatsCommand;
    
    public Ticket ticket;
    
    /**
     * Creates a new instance of BookSeatCommand
     */
    public BookSeatCommand(BufferedReader input, FindSeatsCommand findSeatsCmd) {
        super(input);
        findSeatsCommand = findSeatsCmd;
    }
    
    public void execute() throws SQLException
    {
        HashSet<Seat> seats = findSeatsCommand.getSelectedSeats();
        
        if (seats.size() == 0) {
            System.out.println("-------------------------------");
            findSeatsCommand.execute();
        }
        
        seats = findSeatsCommand.getSelectedSeats();
        
        if (seats.size() == 0)
            return;
        
        TrainRoute route = findSeatsCommand.getSelectedRoute();
        
        // Choose a wagon number from the set
        Seat seat = promptSeatAndWagon(seats);
        System.out.println("-------------------------------");
        
        connection.setAutoCommit(false);
        
        // Check if that the seat is still free inside the transaction.
        for (TrainConnection con : route.connections) {
            String sql = "SELECT EXISTS (SELECT * FROM reservations WHERE departure="+con.departure+" AND wagon_number="+seat.wagonNumber+" AND " +
                    "seat_position='"+seat.position+"' AND seat_row="+seat.row+")";
            ResultSet result = connection.createStatement().executeQuery(sql);
            result.next();
            if (result.getBoolean(1)) {
                connection.setAutoCommit(true);
                System.out.println("Sorry, your seat was just reserved by someone else");
                return;
            }
        }
        
        
        // Book seats for the ticket
        for (TrainConnection con : route.connections) {
            String sql = "INSERT INTO reservations (wagon_number, seat_position, seat_row, departure, to_name, from_name, ticket_id, train_number) VALUES " +
                "("+seat.wagonNumber+", '"+seat.position+"', "+seat.row+", "+con.departure+", '"+escapeSQL(con.to)+"', '"+escapeSQL(con.from)+"', " +
                    "" + ticket.id + ", " +con.trainNumber + ")";
            connection.createStatement().execute(sql);
        }
        
        connection.commit();
        connection.setAutoCommit(true);
        
        System.out.println("Seat " + seat.row + seat.position + " in wagon " + seat.wagonNumber + " has now been reserved on the route: " + route);
        System.out.println("-------------------------------");
    }
}






